﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Gemli.Data;
using Gemli.Data.Providers;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Tests.Gemli.Data
{
    [TestClass]
    public partial class DbDataProviderTest
    {
        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext { get; set; }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        // [ClassInitialize()]
        [TestInitialize()]
        public //static 
            void DataModelDbRepositoryTest_Initialize() //TestContext testContext)
        {
            if (TestContext.DataConnection != null)
            {
                TestSqlConnection = TestContext.DataConnection.ConnectionString;
            }
            using (var conn = new SqlConnection(TestSqlConnection))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "DELETE FROM mock_table";
                    cmd.ExecuteNonQuery();
                }
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText
                        = "SET IDENTITY_INSERT mock_table ON\n"
                          + "INSERT INTO mock_table (customentity_id, string_value, money_value)\n"
                          + "VALUES (1, 'abc', NULL)\n"
                          + "INSERT INTO mock_table (customentity_id, string_value, money_value)\n"
                          + "VALUES (2, 'ghi', 2.2000)\n"
                          + "INSERT INTO mock_table (customentity_id, string_value, money_value)\n"
                          + "VALUES (3, 'def', 2222.0000)\n"
                          + "SET IDENTITY_INSERT mock_table OFF\n";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        [DataModelTableMapping(Table = "mock_table")]
        public class MockPoco 
        {
            [DataModelFieldMapping("customentity_id")] // infer:, IsIdentity = true)]
            public int ID { get; set; }

            [DataModelFieldMapping("string_value")]
            public string MockStringValue { get; set; }

            [DataModelFieldMapping("money_value")]
            public decimal? MockDecimalValue { get; set; }
        }

        private static string _testSqlConnection;
        private static string TestSqlConnection
        {
            get
            {
                if (_testSqlConnection == null)
                {
                    var ret =
                        @"Server=.\SQLExpress;AttachDbFilename={0};Trusted_Connection=Yes;";
                    var execPath = System.Reflection.Assembly.GetExecutingAssembly().Location;
                    var projPath = execPath;
                    while (!projPath.ToLower().EndsWith("bin") && !projPath.ToLower().EndsWith("testresults"))
                    {
                        if (projPath.EndsWith(":")) return execPath;
                        if (projPath.Contains("/")) projPath = projPath.Substring(0, projPath.LastIndexOf("/"));
                        if (projPath.Contains("\\"))
                            projPath = projPath.Substring(0, projPath.LastIndexOf("\\"));
                    }
                    if (projPath.Contains("/")) projPath = projPath.Substring(0, projPath.LastIndexOf("/"));
                    if (projPath.Contains("\\"))
                        projPath = projPath.Substring(0, projPath.LastIndexOf("\\"));
                    var dbPath = projPath 
                        + (projPath.Contains("/") ? "/" : "\\")
                        + "Tests.Gemli.Data"
                        + (projPath.Contains("/") ? "/" : "\\")
                        + @"DBDataProviderTestDB.mdf";
                    _testSqlConnection = string.Format(ret, dbPath);
                    var srcDbPath = dbPath.Replace("DBDataProviderTestDB.mdf", @"EmptyMDF\DBDataProviderTestDB.mdf");
                    var srcLogPath = srcDbPath.Replace("DBDataProviderTestDB.mdf", "DBDataProviderTestDB_log.ldf");
                    var destLogPath = dbPath.Replace("DBDataProviderTestDB.mdf", "DBDataProviderTestDB_log.ldf");
                    SetupTestDB(srcDbPath, srcLogPath, dbPath, destLogPath);
                }
                return _testSqlConnection;
            }
            set
            {
                _testSqlConnection = value;
            }
        }

        [TestMethod]
        public void TestSqlConnectionTest()
        {
            var connString = TestSqlConnection;
            var sqlConnection = new SqlConnection(connString);
            sqlConnection.Open();
            var state = sqlConnection.State;
            Assert.IsTrue(state == ConnectionState.Connecting ||
                          state == ConnectionState.Open);
            sqlConnection.Close();
        }

        [TestMethod]
        public void LoadMockEntityTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var mocks = dbProvider.LoadModels(
                new DataModelQuery<DataModel<MockPoco>>()
                    .WhereProperty["ID"].IsEqualTo(2))
                .Unwrap();
            
            var myMockEntityQuery = DataModel<MockPoco>.NewQuery()
                .WhereProperty["ID"].IsEqualTo(2);
            var entity = dbProvider.LoadModel(myMockEntityQuery);
            Assert.IsNotNull(entity);
            Assert.AreEqual("ghi", entity.Entity.MockStringValue);
            Assert.IsTrue(entity.DataProvider == dbProvider);
        }

        [TestMethod]
        public void LoadMockEntityTest2()
        {
            var sqlFactory = SqlClientFactory.Instance;
            DataProviderBase dbProvider 
                = new DbDataProvider(sqlFactory, TestSqlConnection);

            var myMockEntityQuery = DataModel<MockPoco>
                .NewQuery()
                .WhereMappedColumn["string_value"].IsEqualTo("abc");
            var entity = dbProvider.LoadModel(myMockEntityQuery);
            Assert.IsNotNull(entity);
            Assert.AreEqual("abc", entity.Entity.MockStringValue);
            Assert.IsTrue(entity.DataProvider == dbProvider);
        }

        [TestMethod]
        public void LoadMockEntitiesTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var myMockEntityQuery = DataModel<MockPoco>.NewQuery()
                .WhereMappedColumn["customentity_id"].IsGreaterThan(-1);
            var entities = dbProvider.LoadModels(myMockEntityQuery);
            Assert.IsNotNull(entities);
            Assert.IsTrue(entities.Count > 0);
            Assert.IsTrue(entities.DataProvider == dbProvider);

            var objects = entities.Unwrap();
            /*always true: 
            Assert.IsTrue(entities[0] is DataModel<MockPoco>);*/
            Assert.IsTrue(objects[0] is MockPoco);

            Assert.IsNotNull(objects);
            Assert.IsTrue(objects.Count > 0);
        }

        [TestMethod]
        public void LoadMockEntitiesTest2()
        {
            var dbProvider = new DbDataProvider(SqlClientFactory.Instance, TestSqlConnection);

            var myMockEntityQuery = DataModel<MockPoco>.NewQuery()
                .AddSortItem("MockStringValue", Sort.Descending);
            var entities = dbProvider.LoadModels(myMockEntityQuery);
            Assert.IsNotNull(entities);
            Assert.IsTrue(entities.Count > 0);
            var models = entities.Unwrap<MockPoco>();
            Assert.AreEqual(models[0].MockStringValue, "ghi");
            Assert.AreEqual(models[1].MockStringValue, "def");
            Assert.AreEqual(models[2].MockStringValue, "abc");
        }

        [TestMethod]
        public void SaveModifiedEntityTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var myMockEntityQuery = DataModel<MockPoco>.NewQuery()
                .WhereMappedColumn["string_value"].IsEqualTo("abc");
            var entity = dbProvider.LoadModel(myMockEntityQuery);
            entity.Entity.MockStringValue = "xyz";
            entity.Save();
            
            myMockEntityQuery = DataModel<MockPoco>.NewQuery()
                .WhereMappedColumn["string_value"].IsEqualTo("xyz");
            entity = dbProvider.LoadModel(myMockEntityQuery);

            Assert.IsNotNull(entity);
            Assert.AreEqual("xyz", entity.Entity.MockStringValue);
            Assert.IsTrue(entity.DataProvider == dbProvider);

            entity.Entity.MockStringValue = "abc";
            entity.Save();
        }

        [TestMethod]
        public void SaveModifiedMockEntitiesTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var myMockEntityQuery = DataModel<MockPoco>
                .NewQuery()
                .WhereMappedColumn["customentity_id"].IsGreaterThan(-1);
            var entities = dbProvider.LoadModels(myMockEntityQuery);
            entities[0].Entity.MockStringValue = "jkl";
            entities[1].Entity.MockStringValue = "mno";
            entities[2].Entity.MockStringValue = "pqr";
            entities.Save();

            entities = dbProvider.LoadModels(myMockEntityQuery);
            Assert.IsTrue(entities[0].Entity.MockStringValue == "jkl");
            Assert.IsTrue(entities[1].Entity.MockStringValue == "mno");
            Assert.IsTrue(entities[2].Entity.MockStringValue == "pqr");

            //cleanup
            entities[0].Entity.MockStringValue = "abc";
            entities[1].Entity.MockStringValue = "ghi";
            entities[2].Entity.MockStringValue = "def";
            entities.Save();
        }

        [TestMethod]
        public void CreateAndDeleteEntityTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var mp = new MockPoco {MockStringValue = "xxx"};
            var dew = new DataModel<MockPoco>(mp);
            dew.DataProvider = dbProvider;
            dew.Save();

            var myMockEntityQuery = DataModel<MockPoco>
                .NewQuery()
                .WhereMappedColumn["string_value"].IsEqualTo("xxx");
            var entity = dbProvider.LoadModel(myMockEntityQuery);
            Assert.IsNotNull(entity);
            Assert.IsNotNull(entity.Entity.ID);
            Assert.IsTrue(entity.Entity.ID > 0);
            Assert.IsTrue(entity.Entity.MockStringValue == "xxx");
            entity.MarkDeleted = true;
            entity.Save();

            entity = dbProvider.LoadModel(myMockEntityQuery);
            Assert.IsNull(entity);
        }

        [TestMethod]
        public void CreateAndDeleteEntityTest2()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var mp = new MockPoco { MockStringValue = "xxx" };
            var dew = new DataModel<MockPoco>(mp); // data entity wrapper
            dew.DataProvider = dbProvider;
            dew.Save(); // auto-synchronizes ID
            // or...
            //dbProvider.SaveModel(dew);
            //dew.SynchronizeFields(SyncTo.ClrMembers); // manually sync ID

            // now let's load it and validate that it was saved
            var mySampleEntityQuery = DataModel<MockPoco>.NewQuery()
                .WhereProperty["ID"].IsEqualTo(mp.ID); // mp.ID was inferred as IsIdentity so we auto-returned it on Save()
            var data = dbProvider.LoadModel(mySampleEntityQuery);
            Assert.IsNotNull(data);

            // by the way, you can go back to the POCO type, too
            var mp2 = data.Entity;
            // precompiler-checked: Assert.IsTrue(mp2 is MockPoco);
            Assert.IsTrue(mp2.ID > 0);
            Assert.IsTrue(mp2.MockStringValue == "xxx");

            // test passed, let's delete the test record
            data.MarkDeleted = true;
            data.Save();

            // ... and make sure that it has been deleted
            data = dbProvider.LoadModel(mySampleEntityQuery);
            Assert.IsNull(data);
        }

        [TestMethod]
        public void CreateAndDeleteEntitiesTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var mockPoco1 = new MockPoco { MockStringValue = "MockPoco1_xxx" };
            var mockPoco1_de = new DataModel<MockPoco>(mockPoco1);
            mockPoco1_de.DataProvider = dbProvider;
            mockPoco1_de.Save();
            var mockPoco2 = new MockPoco { MockStringValue = "MockPoco2_xxx" };
            var mockPoco2_de = new DataModel<MockPoco>(mockPoco2);
            mockPoco2_de.DataProvider = dbProvider;
            mockPoco2_de.Save();
            var mockPoco3 = new MockPoco { MockStringValue = "MockPoco3_xxx" };
            var mockPoco3_de = new DataModel<MockPoco>(mockPoco3);
            mockPoco3_de.DataProvider = dbProvider;
            mockPoco3_de.Save();

            var myMockEntityQuery = DataModel<MockPoco>
                .NewQuery()
                .WhereMappedColumn["string_value"].IsLike("%xxx");
            var entities = dbProvider.LoadModels(myMockEntityQuery);
            var e = entities.Unwrap<MockPoco>()[0];

            entities[0].MarkDeleted = true;
            entities[2].MarkDeleted = true;
            entities.Save();

            var entities2 = dbProvider.LoadModels(myMockEntityQuery);
            if (entities2.Count!= 1)
            {
                
            }
            Assert.IsTrue(entities2.Count == 1);

            //cleanup
            entities[1].MarkDeleted = true;
            entities.Save();
        }

        [TestMethod]
        public void SelectWithProcTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var oldMapping = DataModelMap.GetEntityMapping(typeof(MockPoco));
            DataModelMap.MapItems.Remove(typeof (MockPoco));
            var newMapping = DataModelMap.GetEntityMapping(typeof (MockPoco));
            newMapping.TableMapping.SelectProcedure = "sp_mock_table_select";

            try
            {

                var q = DataModel<MockPoco>.NewQuery().WhereProperty["ID"].IsEqualTo(1);
                var entity = dbProvider.LoadModel(q);

                Assert.IsNotNull(entity);
                Assert.IsTrue(entity.Entity.ID == 1);
            }
            finally
            {

                DataModelMap.MapItems.Remove(typeof (MockPoco));
                DataModelMap.MapItems.Add(typeof (MockPoco), oldMapping);
            }
        }

        [TestMethod]
        public void SelectManyWithProcTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var oldMapping = DataModelMap.GetEntityMapping(typeof(MockPoco));
            DataModelMap.MapItems.Remove(typeof(MockPoco));
            var newMapping = DataModelMap.GetEntityMapping(typeof(MockPoco));
            newMapping.TableMapping.SelectManyProcedure = "sp_mock_table_SelectMany";

            try
            {

                var q = DataModel<MockPoco>.NewQuery().AddSortItem("ID", Sort.Descending);
                var entities = dbProvider.LoadModels(q);

                Assert.IsNotNull(entities);
                Assert.IsTrue(entities.Count > 1);
                Assert.IsTrue(entities[0].Entity.ID == 3);

            }
            finally
            {

                DataModelMap.MapItems.Remove(typeof (MockPoco));
                DataModelMap.MapItems.Add(typeof (MockPoco), oldMapping);
            }
        }

        [TestMethod]
        public void SelectManyWithProcTest2()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var oldMapping = DataModelMap.GetEntityMapping(typeof (MockPoco));
            DataModelMap.MapItems.Remove(typeof (MockPoco));
            var newMapping = DataModelMap.GetEntityMapping(typeof (MockPoco));
            newMapping.TableMapping.SelectManyProcedure = "sp_mock_table_SelectMany";

            try
            {

                var q = DataModel<MockPoco>.NewQuery().WhereProperty["ID"].IsEqualTo(3);
                var entities = dbProvider.LoadModels(q);

                Assert.IsNotNull(entities);
                Assert.IsTrue(entities.Count == 1);
                Assert.IsTrue(entities[0].Entity.ID == 3);

            }
            finally
            {

                DataModelMap.MapItems.Remove(typeof (MockPoco));
                DataModelMap.MapItems.Add(typeof (MockPoco), oldMapping);
            }
        }

        [TestMethod]
        public void CreateWithProcAndDeleteEntityTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var oldMapping = DataModelMap.GetEntityMapping(typeof(MockPoco));
            DataModelMap.MapItems.Remove(typeof(MockPoco));
            var newMapping = DataModelMap.GetEntityMapping(typeof(MockPoco));
            newMapping.TableMapping.InsertProcedure = "sp_mock_table_Insert";

            try
            {

                var mp = new MockPoco { MockStringValue = "xxx" };
                var dew = new DataModel<MockPoco>(mp);
                dew.DataProvider = dbProvider;
                dew.Save();

                var myMockEntityQuery = DataModel<MockPoco>
                    .NewQuery()
                    .WhereMappedColumn["string_value"].IsEqualTo("xxx");
                var entity = dbProvider.LoadModel(myMockEntityQuery);
                Assert.IsNotNull(entity);
                Assert.IsNotNull(entity.Entity.ID);
                Assert.IsTrue(entity.Entity.ID > 0);
                Assert.IsTrue(entity.Entity.MockStringValue == "xxx");
                entity.MarkDeleted = true;
                entity.Save();

                entity = dbProvider.LoadModel(myMockEntityQuery);
                Assert.IsNull(entity);

            }
            finally
            {
                DataModelMap.MapItems.Remove(typeof (MockPoco));
                DataModelMap.MapItems.Add(typeof (MockPoco), oldMapping);
            }
        }

        [TestMethod]
        public void SaveModifiedEntityWithProcTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var oldMapping = DataModelMap.GetEntityMapping(typeof(MockPoco));
            DataModelMap.MapItems.Remove(typeof(MockPoco));
            var newMapping = DataModelMap.GetEntityMapping(typeof(MockPoco));
            newMapping.TableMapping.UpdateProcedure = "sp_mock_table_Update";

            try
            {

                var myMockEntityQuery = DataModel<MockPoco>.NewQuery()
                    .WhereMappedColumn["string_value"].IsEqualTo("abc");
                var entity = dbProvider.LoadModel(myMockEntityQuery);
                entity.Entity.MockStringValue = "xyz";
                entity.Save();

                myMockEntityQuery = DataModel<MockPoco>.NewQuery()
                    .WhereMappedColumn["string_value"].IsEqualTo("xyz");
                entity = dbProvider.LoadModel(myMockEntityQuery);

                Assert.IsNotNull(entity);
                Assert.AreEqual("xyz", entity.Entity.MockStringValue);
                Assert.IsTrue(entity.DataProvider == dbProvider);

                entity.Entity.MockStringValue = "abc";
                entity.Save();
            }
            finally
            {

                DataModelMap.MapItems.Remove(typeof (MockPoco));
                DataModelMap.MapItems.Add(typeof (MockPoco), oldMapping);
            }
        }

        [TestMethod]
        public void CreateWithoutProcAndDeleteWithProcEntityTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var oldMapping = DataModelMap.GetEntityMapping(typeof(MockPoco));
            DataModelMap.MapItems.Remove(typeof(MockPoco));
            var newMapping = DataModelMap.GetEntityMapping(typeof(MockPoco));
            newMapping.TableMapping.DeleteProcedure = "sp_mock_table_Delete";

            try
            {

                var mp = new MockPoco { MockStringValue = "xxx" };
                var dew = new DataModel<MockPoco>(mp);
                dew.DataProvider = dbProvider;
                dew.Save();

                var myMockEntityQuery = DataModel<MockPoco>
                    .NewQuery()
                    .WhereMappedColumn["string_value"].IsEqualTo("xxx");
                var entity = dbProvider.LoadModel(myMockEntityQuery);
                Assert.IsNotNull(entity);
                Assert.IsNotNull(entity.Entity.ID);
                Assert.IsTrue(entity.Entity.ID > 0);
                Assert.IsTrue(entity.Entity.MockStringValue == "xxx");
                entity.MarkDeleted = true;
                entity.Save();

                entity = dbProvider.LoadModel(myMockEntityQuery);
                Assert.IsNull(entity);

            }
            finally
            {
                DataModelMap.MapItems.Remove(typeof(MockPoco));
                DataModelMap.MapItems.Add(typeof(MockPoco), oldMapping);
            }
        }

        [DataModelTableMapping("company")]
        public class Company
        {
            [DataModelFieldMapping("company_id",
                IsPrimaryKey = true, DataType = DbType.Int32)]
            public int ID { get; set; }

            [DataModelFieldMapping("name")]
            public string CompanyName { get; set; }
            [ForeignDataModel(Relationship = Relationship.OneToMany)]
            public List<Contact> Contacts { get; set; }
        }

        [DataModelTableMapping("contact")]
        public class Contact : DataModel
        {
            [DataModelFieldMapping("contact_id", IsPrimaryKey = true)]
            public int ID
            {
                get { return (int)base["ID"]; }
                set { base["ID"] = value; }
            }
            [DataModelFieldMapping("name")]
            public string Name
            {
                get { return (string)base["Name"]; }
                set { base["Name"] = value; }
            }

            [DataModelFieldMapping("phone")]
            public string Phone
            {
                get { return (string)base["Phone"]; }
                set { base["Phone"] = value; }
            }
            [ForeignDataModel(ColumnName = "company_id",
                Relationship = Relationship.ManyToOne)]
            public Company Company { get; set; }
        }

        [TestMethod]
        public void DeepLoadEntityOneToManyTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var query = new DataModelQuery<DataModel<Company>>()
                .WhereProperty["ID"].IsEqualTo(1);
            var decompany = dbProvider.DeepLoadModel(query) as DataModel<Company>;
            Assert.IsNotNull(decompany);
            Company company = decompany.Entity;
            Assert.IsNotNull(company);
            Assert.IsNotNull(company.Contacts, "Contacts not populated");
            Assert.IsTrue(company.Contacts.Count == 2, company.Contacts.Count
                + " loaded (expected 2).");
            Assert.IsTrue(company.Contacts[0].Name == "Betty Sue" ||
                          company.Contacts[0].Name == "John Doe");
            Assert.IsTrue(company.Contacts[1].Name == "Betty Sue" ||
                          company.Contacts[1].Name == "John Doe");
        }

        [TestMethod]
        public void DeepLoadEntityOneToManyTest2()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var query = new DataModelQuery<DataModel<Company>>()
                .WhereProperty["ID"].IsEqualTo(2);
            var decompany = dbProvider.DeepLoadModel(query) as DataModel<Company>;
            Assert.IsNotNull(decompany);
            Company company = decompany.Entity;
            Assert.IsNotNull(company);
            Assert.IsNotNull(company.Contacts, "Contacts not populated");
            Assert.IsTrue(company.Contacts.Count == 2, company.Contacts.Count.ToString() + " loaded (expected 2).");
            Assert.IsTrue(company.Contacts[0].Name == "Bobby Joe" ||
                          company.Contacts[0].Name == "Jane Lane");
            Assert.IsTrue(company.Contacts[1].Name == "Bobby Joe" ||
                          company.Contacts[1].Name == "Jane Lane");
        }

        [TestMethod]
        public void DeepLoadEntityManyToOneTest1()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);
            var query = new DataModelQuery<Contact>()
                .WhereProperty["ID"].IsEqualTo(1);
            var contact = dbProvider.DeepLoadModel(query) as Contact;
            //Assert.IsNotNull(decontact);
            //var contact = decontact.DataInstance;
            Assert.IsNotNull(contact);
            Assert.IsTrue(contact.Name == "Bobby Joe");
            Assert.IsNotNull(contact.Company);
            Assert.IsTrue(contact.Company.CompanyName == "Foobar, Ltd.");
        }

        [TestMethod]
        public void DeepLoadEntityManyToOneTest2()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var query = new DataModelQuery<Contact>()
                .WhereProperty["ID"].IsEqualTo(2);
            var contact = dbProvider.DeepLoadModel(query) as Contact;
            //Assert.IsNotNull(decontact);
            //var contact = decontact.DataInstance;
            Assert.IsNotNull(contact);
            Assert.IsTrue(contact.Name == "Betty Sue");
            Assert.IsNotNull(contact.Company);
            Assert.IsTrue(contact.Company.CompanyName == "Acme, Inc.");
        }

        [TestMethod]
        public void DeepLoadEntityManyToOneTest3()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var query = new DataModelQuery<Contact>()
                .WhereProperty["ID"].IsEqualTo(3);
            var contact = dbProvider.DeepLoadModel(query) as Contact;
            //Assert.IsNotNull(decontact);
            //var contact = decontact.DataInstance;
            Assert.IsNotNull(contact);
            Assert.IsTrue(contact.Name == "John Doe");
            Assert.IsNotNull(contact.Company);
            Assert.IsTrue(contact.Company.CompanyName == "Acme, Inc.");
        }

        [TestMethod]
        public void DeepLoadEntityManyToOneTest4()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var query = new DataModelQuery<Contact>()
                .WhereProperty["ID"].IsEqualTo(4);
            var contact = dbProvider.DeepLoadModel(query) as Contact;
            //Assert.IsNotNull(decontact);
            //var contact = decontact.DataInstance;
            Assert.IsNotNull(contact);
            Assert.IsTrue(contact.Name == "Jane Lane");
            Assert.IsNotNull(contact.Company);
            Assert.IsTrue(contact.Company.CompanyName == "Foobar, Ltd.");
            Assert.IsTrue(contact.Company.Contacts != null);
            Assert.IsTrue(contact.Company.Contacts.Count > 0);
        }

        [TestMethod]
        public void DeepSaveEntityTest()
        {
            // todo: test all four relationship types

            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var query = new DataModelQuery<DataModel<Company>>()
                .WhereProperty["ID"].IsEqualTo(1);
            var decompany = dbProvider.DeepLoadModel(query);
            var company = decompany.Entity;
            company.CompanyName += "_";
            foreach (var c in company.Contacts)
                c.Name += "_";
            decompany.Save(true);

            query = new DataModelQuery<DataModel<Company>>()
                .WhereProperty["ID"].IsEqualTo(1);
            decompany = dbProvider.DeepLoadModel(query);
            company = decompany.Entity;
            Assert.IsTrue(company.CompanyName.Length > 1 &&
                          company.CompanyName.EndsWith("_"));
            Assert.IsTrue(company.Contacts[0]
                              .Name.Length > 1 &&
                          company.Contacts[0]
                              .Name.EndsWith("_"));
            Assert.IsTrue(company.Contacts[company.Contacts.Count - 1]
                  .Name.Length > 1 &&
              company.Contacts[company.Contacts.Count - 1]
                  .Name.EndsWith("_"));

            ReloadData_CompanyContact(); // clean-up

        }

        [TestMethod]
        public void DeepSaveEntitiesTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var query = new DataModelQuery<DataModel<Company>>()
                .WhereProperty["ID"].IsEqualTo(1);
            var decompany = dbProvider.DeepLoadModel(query);
            var company = decompany.Entity;
            company.CompanyName += "_";
            foreach (var c in company.Contacts)
                c.Name += "_";

            var query2 = new DataModelQuery<DataModel<Company>>()
                .WhereProperty["ID"].IsEqualTo(2);
            var decompany2 = dbProvider.DeepLoadModel(query2);
            var company2 = decompany2.Entity;
            company2.CompanyName = "_" + company2.CompanyName;
            foreach (var c in company2.Contacts)
                c.Name = "_" + c.Name;

            var col = new DataModelCollection<DataModel<Company>>();
            col.Add(decompany);
            col.Add(decompany2);
            col.DataProvider = dbProvider;

            col.Save(true);

            query = new DataModelQuery<DataModel<Company>>()
                .WhereProperty["ID"].IsEqualTo(1);
            decompany = dbProvider.DeepLoadModel(query);
            company = decompany.Entity;
            Assert.IsTrue(company.CompanyName.Length > 1 &&
                          company.CompanyName.EndsWith("_"));
            Assert.IsTrue(company.Contacts[0]
                              .Name.Length > 1 &&
                          company.Contacts[0]
                              .Name.EndsWith("_"));
            Assert.IsTrue(company.Contacts[company.Contacts.Count - 1]
                  .Name.Length > 1 &&
              company.Contacts[company.Contacts.Count - 1]
                  .Name.EndsWith("_"));

            query = new DataModelQuery<DataModel<Company>>()
                .WhereProperty["ID"].IsEqualTo(2);
            decompany = dbProvider.DeepLoadModel(query);
            company = decompany.Entity;
            Assert.IsTrue(company.CompanyName.Length > 1 &&
                          company.CompanyName.StartsWith("_"));
            Assert.IsTrue(company.Contacts[0]
                              .Name.Length > 1 &&
                          company.Contacts[0]
                              .Name.StartsWith("_"));
            Assert.IsTrue(company.Contacts[company.Contacts.Count - 1]
                  .Name.Length > 1 &&
              company.Contacts[company.Contacts.Count - 1]
                  .Name.StartsWith("_"));

            ReloadData_CompanyContact(); // clean-up
        }

        //[DataModelTableMapping("Group")]
        public class Group
        {
            [DataModelFieldMapping("group_id", IsPrimaryKey = true)]
            public Guid ID { get; set; }
            [DataModelFieldMapping("group_name")]
            public string Name { get; set; }
            [ForeignDataModel(Relationship = Relationship.ManyToMany,
                MappingTable = "GroupUser")]
            public List<User> Users { get; set; }
        }

        //[DataModelTableMapping("User")]
        public class User
        {
            [DataModelFieldMapping("user_id", IsPrimaryKey = true)]
            public Guid ID { get; set; }
            [DataModelFieldMapping("user_name")]
            public string Name { get; set; }
            [ForeignDataModel(Relationship = Relationship.ManyToMany,
                MappingTable = "GroupUser")]
            public List<Group> Groups { get; set; }
        }

        [TestMethod]
        public void DeepLoadEntityManyToManyTest()
        {
            var sqlFactory = SqlClientFactory.Instance;
            var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

            var query = new DataModelQuery<DataModel<Group>>();
            var egroups = dbProvider.DeepLoadModels(query);
            var groups = new DataModelCollection<DataModel<Group>>(egroups);

            Assert.IsTrue(groups.Count == 2, "Groups did not load");
            Assert.IsNotNull(groups[0].Entity, "Group 0 DataInstance was not set");
            Assert.IsNotNull(groups[0].Entity.Users, "Group 0 Users were not set");
            Assert.IsTrue(groups[0].Entity.Users.Count == 2, "Users count (group 0 of 0,1) is not 2");
            Assert.IsNotNull(groups[1].Entity, "Group 1 DataInstance was not set");
            Assert.IsNotNull(groups[0].Entity.Users, "Group 1 Users were not set");
            Assert.IsTrue(groups[1].Entity.Users.Count == 2, "Users count (group 1 of 0,1) is not 2");

            Assert.IsTrue(groups[0].Entity.Users[0].ID
                       != groups[1].Entity.Users[0].ID, "Same user loaded between groups");
            Assert.IsTrue(groups[0].Entity.Users[0].ID
                       != groups[1].Entity.Users[1].ID, "Same user loaded between groups");
            Assert.IsTrue(groups[1].Entity.Users[0].ID
                       != groups[0].Entity.Users[0].ID, "Same user loaded between groups");
            //Assert.IsTrue(groups[1].DataInstance.Users[0].ID
            //           != groups[0].DataInstance.Users[1].ID, "Same user loaded between groups");
        }

        //private static DataTable CreateAndPopulateMockTable(string tableName)
        //{
        //    var ret = new DataTable(tableName);
        //    switch (tableName)
        //    {
        //        // one-to-many
        //        case "Company":
        //            var companyIdCol = new DataColumn("company_id", typeof (int));
        //            ret.Columns.Add(companyIdCol);
        //            var companyNameCol = new DataColumn("company_name", typeof (string));
        //            ret.Columns.Add(companyNameCol);
        //            break;
        //        // one-to-many
        //        case "Contact":
        //            var contactIdCol = new DataColumn("contact_id", typeof (int));
        //            ret.Columns.Add(contactIdCol);
        //            var contactNameCol = new DataColumn("contact_name", typeof (string));
        //            ret.Columns.Add(contactNameCol);
        //            var contactPhoneCol = new DataColumn("contact_phone", typeof (string));
        //            ret.Columns.Add(contactPhoneCol);
        //            var contactCompanyIdCol = new DataColumn("company_id", typeof(int));
        //            ret.Columns.Add(contactCompanyIdCol);
        //            break;
        //        // many-to-many
        //        case "User":
        //            var userIdCol = new DataColumn("user_id", typeof (Guid));
        //            ret.Columns.Add(userIdCol);
        //            var userNameCol = new DataColumn("user_name", typeof (string));
        //            ret.Columns.Add(userNameCol);
        //            break;
        //        // many-to-many
        //        case "Group":
        //            var groupIdCol = new DataColumn("group_id", typeof (Guid));
        //            ret.Columns.Add(groupIdCol);
        //            var groupNameCol = new DataColumn("group_name", typeof (string));
        //            ret.Columns.Add(groupNameCol);
        //            break;
        //        // many-to-many
        //        case "GroupUser":
        //            var groupUserIdCol = new DataColumn("groupuser_id", typeof (long));
        //            groupUserIdCol.AutoIncrement = true;
        //            ret.Columns.Add(groupUserIdCol);
        //            var groupUser_userIdCol = new DataColumn("user_id", typeof(Guid));
        //            ret.Columns.Add(groupUser_userIdCol);
        //            var groupUser_groupIdCol = new DataColumn("group_id", typeof (Guid));
        //            ret.Columns.Add(groupUser_groupIdCol);
        //            break;
        //        default:
        //            break;
        //    }
        //    PopulateMockTable(ret);
        //    return ret;
        //}
        //private static void PopulateMockTable(DataTable table)
        //{
        //    DataRow row;
        //    switch (table.TableName)
        //    {
        //        // one-to-many
        //        case "Company":
        //            table.BeginLoadData();
        //            row = table.NewRow();
        //            row["company_id"] = 1;
        //            row["company_name"] = "Acme, Inc.";
        //            table.Rows.Add(row);
        //            row = table.NewRow();
        //            row["company_id"] = 2;
        //            row["company_name"] = "Foobar, Ltd.";
        //            table.Rows.Add(row);
        //            table.EndLoadData();
        //            break;
        //        // one-to-many
        //        case "Contact":
        //            table.BeginLoadData();
        //            row = table.NewRow();
        //            row["contact_id"] = 1;
        //            row["contact_name"] = "Bobby Joe";
        //            row["contact_phone"] = "123-456-7890";
        //            row["company_id"] = 2;
        //            table.Rows.Add(row);
        //            row = table.NewRow();
        //            row["contact_id"] = 2;
        //            row["contact_name"] = "Betty Sue";
        //            row["contact_phone"] = "987-654-3210";
        //            row["company_id"] = 1;
        //            table.Rows.Add(row);
        //            row = table.NewRow();
        //            row["contact_id"] = 3;
        //            row["contact_name"] = "John Doe";
        //            row["contact_phone"] = "444-444-4444";
        //            row["company_id"] = 1;
        //            table.Rows.Add(row);
        //            row = table.NewRow();
        //            row["contact_id"] = 4;
        //            row["contact_name"] = "Jane Lane";
        //            row["contact_phone"] = "898-989-8989";
        //            row["company_id"] = 2;
        //            table.Rows.Add(row);
        //            table.EndLoadData();
        //            break;
        //        // many-to-many
        //        case "User":
        //            table.BeginLoadData();
        //            row = table.NewRow();
        //            row["user_id"] = new Guid("AD62B917-0EF9-48e1-980B-3A717E329E2E");
        //            row["user_name"] = "Bob";
        //            table.Rows.Add(row);
        //            row = table.NewRow();
        //            row["user_id"] = new Guid("5A401D28-C2CA-4b3b-9573-D24D2E3FDF27");
        //            row["user_name"] = "Chris";
        //            table.Rows.Add(row);
        //            row = table.NewRow();
        //            row["user_id"] = new Guid("7EBC702F-C83A-4f8f-879B-F7D800CF390A");
        //            row["user_name"] = "Fred";
        //            table.Rows.Add(row);
        //            table.EndLoadData();
        //            break;
        //        // many-to-many
        //        case "Group":
        //            table.BeginLoadData();
        //            row = table.NewRow();
        //            row["group_id"] = new Guid("BF59F59C-26BA-4e31-BEB0-024B5A219D6D");
        //            row["group_name"] = "AppUsers";
        //            table.Rows.Add(row);
        //            row = table.NewRow();
        //            row["group_id"] = new Guid("721BCF79-C721-4413-A7EA-F0A75D5E6AA2");
        //            row["group_name"] = "Administrators";
        //            table.Rows.Add(row);
        //            table.EndLoadData();
        //            break;
        //        // many-to-many
        //        case "GroupUser":
        //            table.BeginLoadData();
        //            row = table.NewRow(); // Bob -> AppUser
        //            row["user_id"] = new Guid("AD62B917-0EF9-48e1-980B-3A717E329E2E");
        //            row["group_id"] = new Guid("BF59F59C-26BA-4e31-BEB0-024B5A219D6D");
        //            table.Rows.Add(row);
        //            row = table.NewRow(); // Chris -> AppUser
        //            row["user_id"] = new Guid("5A401D28-C2CA-4b3b-9573-D24D2E3FDF27");
        //            row["group_id"] = new Guid("BF59F59C-26BA-4e31-BEB0-024B5A219D6D");
        //            table.Rows.Add(row);
        //            row = table.NewRow(); // Chris -> Administrator
        //            row["user_id"] = new Guid("5A401D28-C2CA-4b3b-9573-D24D2E3FDF27");
        //            row["group_id"] = new Guid("721BCF79-C721-4413-A7EA-F0A75D5E6AA2");
        //            table.Rows.Add(row);
        //            row = table.NewRow(); // Fred -> Administrator
        //            row["user_id"] = new Guid("7EBC702F-C83A-4f8f-879B-F7D800CF390A");
        //            row["group_id"] = new Guid("721BCF79-C721-4413-A7EA-F0A75D5E6AA2");
        //            table.Rows.Add(row);
        //            table.EndLoadData();
        //            break;
        //        default:
        //            var col = new DataColumn("customentity_id", typeof (int)) {AutoIncrement = true};
        //            table.Columns.Add(col);
        //            col = new DataColumn("string_value", typeof (string));
        //            table.Columns.Add(col);
        //            col = new DataColumn("money_value", typeof (decimal));
        //            table.Columns.Add(col);
        //            table.BeginLoadData();
        //            row = table.NewRow(); // 0
        //            row["string_value"] = "abc";
        //            row["money_value"] = "4.25";
        //            table.Rows.Add(row);
        //            row = table.NewRow(); // 1
        //            row["string_value"] = "def";
        //            row["money_value"] = "2.76";
        //            table.Rows.Add(row);
        //            row = table.NewRow(); // 2
        //            row["string_value"] = "ghi";
        //            row["money_value"] = "3.99";
        //            table.Rows.Add(row);
        //            row = table.NewRow(); // 3
        //            row["string_value"] = "jkl";
        //            row["money_value"] = "9.49";
        //            table.Rows.Add(row);
        //            table.EndLoadData();
        //            break;
        //    }
        //}
    }
}
